<?php
	class PixelManager extends Model_Abstract{
		public function add($cam, $pub, $pla, $peak)
		{
			$sql = "INSERT INTO `pixel_managment` SET `campaign`=?, `pub`=?, `pla`=?, `peak`=?";
			try{
				return $this->_connection->execute($sql, array($cam, $pub, $pla, $peak));
			}catch (Exception $e){
				QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
				throw new Model_Exception($e->getMessage(), $e->getCode());
			}
		}
		
		public function getPixelManager()
		{
			$now = time();
			$today_7 = mktime(0, 0, 0, date('m',$now),date('d',$now),date('Y',$now)) +  (Q::ini('appini/pixelmanager_time')*60*60);
			if($now >= $today_7){	
				$data_timestart = date('Y-m-d H:i:s', mktime(0, 0, 0, date('m',$now), date('d',$now)-1, date('Y',$now)));
				$data_timeend = date('Y-m-d H:i:s', mktime(23, 59, 59, date('m',$now), date('d',$now)-1, date('Y',$now)));
			}else{
				$data_timestart = date('Y-m-d H:i:s', mktime(0, 0, 0, date('m',$now), date('d',$now)-2, date('Y',$now)));
				$data_timeend = date('Y-m-d H:i:s', mktime(23, 59, 59, date('m',$now), date('d',$now)-2, date('Y',$now)));
			}
			
			$sql = "SELECT SUBSTRING_INDEX(s.time_new, ' ', 1) AS t, p.campaign AS cam, p.pub, p.pla, p.peak, SUM(s.cre) AS cre, 
						   SUM(s.impression) AS impression, SUM(s.conversion) AS conversion, SUM(s.revenue) AS revenue, 
						   SUM(s.suppress) AS suppress, SUM(s.spend) AS cost, SUM(s.click) AS click 
					FROM `pixel_managment` AS p LEFT JOIN `site_data` AS s 
					ON (s.time_new>=? AND s.time_new<=?) WHERE p.campaign = s.cam AND p.pub = s.pub AND p.pla = s.pla  GROUP BY t, cam, pub, pla ";
			try{
				$result = $this->_connection->query($sql, array($data_timestart, $data_timeend));
				return $result;
				
//				$groupbyCols=array('t', 'cam', 'pub', 'pla');
//				$sumCols=array('impression', 'landing', 'conversion', 'revenue', 'suppress', 'cost', 'click');
//				$resultGrouped = $this->groupBy_Cols($result, $groupbyCols, $sumCols);
//
//				return $resultGrouped;
			}catch (Exception $e){
				QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
				throw new Model_Exception($e->getMessage(), $e->getCode());
			}
		}
		
		public function modifyPeak($id, $peak)
		{
			$sql = "UPDATE `pixel_managment` SET `peak`=? WHERE id=?";
			try{
				return $this->_connection->execute($sql, array($peak, $id));
			}catch (Exception $e){
				QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
				throw new Model_Exception($e->getMessage(), $e->getCode());
			}
		}
		
		public function tblGetAll( $field_conditions )
		{
			$query = array();
			$vars = array();
			foreach($field_conditions as $field => $condition){
				$query[] = $field . " LIKE ? ";
				$vars[] = '%' . $condition . '%';
			}
			if(!empty($query)){
				$sqlWhere = ' WHERE ' . implode(' AND ', $query);
			}
			$sql = " SELECT * FROM `pixel_managment` " . $sqlWhere;
			try {
				return $this->_connection->query($sql, $vars);
			} catch (Exception $e) {
				Qlog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), Qlog::ERR);
				throw new Model_Exception($e->getMessage, $e->getCode());
			}
		}
		
		public function tblGetById($id)
		{
			$sql = "SELECT * FROM `pixel_managment` WHERE id=?";
			try {
				$reuslt = $this->_connection->query($sql, array($id));
				if (count($reuslt) > 0) {
					return $reuslt[0];
				}
				return null;
			} catch (Exception $e) {
				Qlog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), Qlog::ERR);
				throw new Model_Exception($e->getMessage, $e->getCode());
			}
		}
		
		public function tblDeleteById($id)
		{
			$sql = 'DELETE FROM `pixel_managment` WHERE id=?';
			try {
				return $this->_connection->execute($sql, array($id));
			} catch (Exception $e) {
				Qlog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), Qlog::ERR);
				throw new Model_Exception($e->getMessage, $e->getCode());
			}
		}
		
		public function tblUpdateById($cam, $pub, $pla, $peak, $id)
		{
			$sql = "UPDATE `pixel_managment` SET `campaign`=?, `pub`=?, `pla`=?, `peak`=? WHERE id=?";
			try{
				return $this->_connection->execute($sql, array($cam, $pub, $pla, $peak, $id));
			}catch (Exception $e){
				QLog::log(__CLASS__.' '.__METHOD__.' --- '.$e->getMessage(), QLog::ERR);
				throw new Model_Exception($e->getMessage(), $e->getCode());
			}
		}
		
		
		private function groupBy_Cols($result, $groupbyCols=array(), $sumCols=array())
		{
			$records=array();
			foreach($result as $k=>$r){
				$allflag=false;
				foreach($records as $kk=>$rr){
					$colsflag=true;
					foreach($groupbyCols as $groupbyCol){
						if($r[$groupbyCol] != $rr[$groupbyCol]){
							$colsflag=false;
							break;
						}
					}	
					if($colsflag){
						foreach($sumCols as $sumCol){
							$records[$kk][$sumCol] = $records[$kk][$sumCol]*1 + $r[$sumCol]*1;
						}
						
						$allflag=true;
						break;
					}					
				}
				if(!$allflag){
					$records[]=$r;	
				}
			}
			return $records;		
		}
	}
?>